# Copyright (c) 2010, College Center for Library Automation

# See the included Copyright.txt file for full license

#
# contains commonly used variables and subroutines for Automated Vendor Stats
# additional comments will sources safe notice this change?
#
use strict;
use warnings;

package avsCommon;

use DBI;
use DBD::ODBC;
use Date::Calc qw(Decode_Month Today Add_Delta_YM check_date Delta_Days Add_Delta_Days);

use base 'Exporter';

our @ISA = qw(Exporter);
our @EXPORT = qw(paramscheck dbsetup dbshutdown myperiod urlencode savecontent
	$sthlogin $sthstats $dbhstats $dbhestats
	@begdate @enddate $begyear $begmonth $begday $endyear $endmonth $endday
	$vendorid $collegeid $username $password $option1 $option2 $vendorusername $vendorpassword
	$SESSIONS $SEARCHES $EBOOKS $EAUDIO $EJOURNAL $EVIDEO $REQUESTS $CLICKTHRU );
our @EXPORT_OK = qw();
    
our ($sthlogin, $sthstats, $dbhstats, $dbhestats);
our (@begdate, @enddate, $begyear, $begmonth, $begday, $endyear, $endmonth, $endday);
our ($vendorid, $collegeid, $username, $password, $option1, $option2, $vendorusername, $vendorpassword);
my ($dbhlogin, $sqllogin, $sqlstats, $sqlestats);

$0 =~ /(\w*?.pl$)/;
my $scriptname = $1;

# constants for use by statstype 
our $SESSIONS	= 1;
our $SEARCHES	= 2;
our $EBOOKS	= 3;
our $EAUDIO	= 4;
our $EJOURNAL	= 5;
our $EVIDEO	= 6;
our $REQUESTS	= 7;
our $CLICKTHRU = 8;

print "#info: executing script $scriptname\n";
print "#info: vendornum collegenum date datasource sessions searches\n";

# check the parameters passed to program. expects [startdate yyyy-m-d] [enddate] [collegeid]. defaults to last month and all colleges
# for any information not passed in.
sub paramscheck {
	my $runforcollegeid='';

	foreach my $arg (@ARGV) {
		if ($arg =~ /^(?<year>\d{4})-(?<month>\d+)-\d+$/) {

			if (@begdate) {
				@enddate = ($+{year}, $+{month}, 1);
			}

			else {
				@begdate = ($+{year}, $+{month}, 1);
			}
		}

		elsif ($arg =~ /^(?<runforcollegeid>\d{1,2}-\d{1,2})|(?<runforcollegeid>\d{1,2})$/) {
			$runforcollegeid = $+{runforcollegeid}
		}

		else {
			die '#error: enter dates as yyyy-mm-dd. arguments expected are: [start date] [end date] [collegeid[-collegeid]], found:', @ARGV;
		}
	}

	# if nothing passed in set the dates to last month.
	if (! @begdate) {
		my ($tyear, $tmonth, $tday) = Today;
		@begdate = Add_Delta_YM($tyear, $tmonth, 1, 0, -1);	# find previous month by subtracting 1 month from this month and setting day to 1
	}

	if (! @enddate) {
		@enddate = @begdate;
	}

	# if date in future or invalid or out of order, punt
	if (! check_date(@begdate) || Delta_Days(@begdate, Today) < 0 || ! check_date(@enddate) || Delta_Days(@enddate, Today) < 0 || Delta_Days(@begdate, @enddate) < 0) {
		die "#error: Start or end date error. Either invalid, in future or out of order @begdate and @enddate\n";
	}
	
	# save the parts for those programs that need all the parts. the day is typically ignored
	($begyear, $begmonth, $begday) = @begdate;
	@enddate = Add_Delta_Days(Add_Delta_YM($enddate[0], $enddate[1], 1, 0, 1), -1); # find last day of month by going to first day of next month and moving back one day
	($endyear, $endmonth, $endday) = @enddate;

	print "#info: Start and end dates set to : @begdate and @enddate\n";
	if ($runforcollegeid) {
		print "#info: specific college id chosen: $runforcollegeid\n";
	}
	
	return $runforcollegeid;
}

# builds database connections, parameterized queries and column bindings based on the vendor name passed
sub dbsetup {

	my $scraper = shift;
	if (! defined $scraper) {
		$scraper = $scriptname;
	}
	
	my $runforcollegeid = paramscheck();
	
	$dbhstats = DBI->connect('dbi:ODBC:AutomatedVendorStats' ,,, { 'PrintError' => 1, 'RaiseError' => 1 })
		or die '#error: dbhlogin connect: ' . DBI->errstr;
	# build stored procedure with parameters: vendorid, collegeid, datasource, period, quantity, statstype (1-4)
	$sqlstats = qq/EXEC InsertStats ?,?,?,?,?,?/;
	$sthstats = $dbhstats->prepare($sqlstats);

	# connect to database, build sql
#	$dbhstats = DBI->connect('dbi:ODBC:driver={SQL Native Client};Server=pepelepew\SQLEXPRESS;Database=VendorStats;;', 'VDBAdmin', 'password', { 'PrintError' => 1, 'RaiseError' => 1 })
	$dbhlogin = DBI->connect('dbi:ODBC:AutomatedVendorStats' ,,, {PrintError => 1, RaiseError => 1})
		or die '#error: dbhlogin connect: ' . DBI->errstr;

	#build the sql to extract all the login information for all colleges for this vendor	
	$sqllogin = qq/
SELECT
	L.VendorId, L.CollegeId, L.UserName, L.Password, L.Option1, L.Option2, V.Username AS VendorUsername, V.Password AS VendorPassword
FROM
	LoginInformation AS L INNER JOIN
	Vendors AS V ON L.VendorId = V.VendorId
WHERE
	L.StartDate <= CONVERT(DATETIME, ?, 102) AND
	L.EndDate >= CONVERT(DATETIME, ?, 102) AND 
	V.Scraper = ? AND
	L.CollegeId BETWEEN ? AND ?
ORDER BY L.CollegeId/;

	# handle collegeid passed as parameter, otherwise select all colleges.
	my $collegeidstart=1;
	my $collegeidend=29;
	
	if ($runforcollegeid) {
		if (length $runforcollegeid > 2) {
			$runforcollegeid =~ /(\d{1,2})-(\d{1,2})/;
			$collegeidstart = $1;
			$collegeidend = $2
		}
		else {
			$collegeidstart = $collegeidend = $runforcollegeid;
		}
	};
	
	$sthlogin = $dbhlogin->prepare($sqllogin)
		or die "#error: dbhlogin prepare:" . $dbhlogin->errstr;
	$sthlogin->execute(join('-', @begdate), join('-', @enddate), $scraper, $collegeidstart, $collegeidend)
		or die "#error: vsCom.pm execute scraper($scraper):" . $dbhlogin->errstr;
		
	$sthlogin->bind_columns(\$vendorid, \$collegeid, \$username, \$password, \$option1, \$option2, \$vendorusername, \$vendorpassword)
		or die "#error: vsCom.pm bind sthlogin: ".$dbhlogin->errstr;
	$sthlogin->fetch
		or die "#error: unable to retrieve login information for $scraper ", ($runforcollegeid) ? "and collegeid $runforcollegeid" : '';
}

sub dbshutdown {
	$sthlogin->finish if ($sthlogin);
	$sthstats->finish if ($sthstats);
	$dbhlogin->disconnect if ($dbhlogin);
	$dbhstats->disconnect if ($dbhstats);
};

# send in y,m return yyyy-mm-01 . m can be month name or number
sub myperiod {
	my $year = shift;
	my $month = shift;
	
	return sprintf('%04d-%02d-01', $year, (length($month) > 2) ? Decode_Month($month) : $month);
};

sub urlencode {
  my $str = shift;
  $str =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;

  return $str;
}

# save contents for possible debugging
sub savecontent {
	my $vendor = shift;
	my $textbreak = shift;
	my $filename = "../DataDownloads/$vendor/" . sprintf("%4d%02d%02d",Today) . '.txt';

	# create directory if needed	
	mkdir ("../DataDownloads/$vendor", '755') if (! -e "../DataDownloads/$vendor");

	# create or append file as needed
	my $filecmd = (-e $filename) ? '>>' : '>';

	open AFILE, $filecmd . $filename;
	print AFILE '#next # # # # # # # # # # ' . $textbreak .' # ' . scalar localtime() . " # # # # # # # # # # \n";
	print AFILE $_[0]. "\n";
	close AFILE;

}
	
1;

__END__